package com.ita.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.ita.dao.CustomerDao;
import com.ita.pojo.Customer;
import com.ita.util.DBUtil;

public class CustomerDaoImpl implements CustomerDao {

	/***
	 * return 1 if success
	 */
	@Override
	public int addCustomer(Customer c) {

		String sql = "insert into customer values(seq01.nextval,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement pst = null;
		int result = 0;
		try {
			con = DBUtil.createByJNDI();
			pst = con.prepareStatement(sql);
			pst.setString(1, c.getName());
			pst.setDouble(2, c.getSal());
			pst.setString(3, c.getSex());
			pst.setDate(4, new java.sql.Date(c.getBirth().getTime()));
			pst.setString(5, c.getFavs());
			result = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		}
		return result;
	}

	/***
	 * return 1 if success
	 */
	@Override
	public int delCustomer(Integer id) {
		
		String sql = "delete from customer where id=?";
		Connection con = null;
		PreparedStatement pst = null;
		int result=0;
		try {
			con = DBUtil.createByJNDI();
			pst = con.prepareStatement(sql);
			pst.setInt(1, id);
			result = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		}
		return result;
	}

	@Override
	public List<Customer> findAllCustomer() {
		List<Customer> list = new ArrayList<Customer>();
		String sql = "select * from customer";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			con = DBUtil.createByJNDI();
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				Customer u = new Customer();
				u.setId(rs.getInt("id"));
				u.setName(rs.getString("name"));
				u.setSal(rs.getDouble("sal"));
				u.setSex(rs.getString("sex"));
				Date birth = new Date(rs.getDate("birth").getTime());
				u.setBirth(birth);
				u.setFavs(rs.getString("favs"));
				list.add(u);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, rs);
		}
		return list;
	}

	@Override
	public Customer findCustomer(Integer id) {
		String sql = "select * from customer where id=?";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		Customer c = null;
		try {
			con = DBUtil.createByJNDI();
			pst = con.prepareStatement(sql);
			pst.setInt(1, id);
			rs = pst.executeQuery();
			if (rs.next()) {
				c = new Customer();
				c.setId(rs.getInt("id"));
				c.setName(rs.getString("name"));
				c.setSal(rs.getDouble("sal"));
				c.setSex(rs.getString("sex"));
				Date birth = new Date(rs.getDate("birth").getTime());
				c.setBirth(birth);
				c.setFavs(rs.getString("favs"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, rs);
		}
		return c;
	}

	/***
	 * return 1 if success
	 */
	@Override
	public int updateCustomer(Customer c) {
		String sql = "update customer set name=?, sal=?, sex=?, birth=?, favs=? where id=?";
		Connection con = null;
		PreparedStatement pst = null;
		int result = 0;
		try {
			con = DBUtil.createByJNDI();
			pst = con.prepareStatement(sql);
			pst.setString(1, c.getName());
			pst.setDouble(2, c.getSal());
			pst.setString(3, c.getSex());
			pst.setDate(4, new java.sql.Date(c.getBirth().getTime()));
			pst.setString(5, c.getFavs());
			pst.setInt(6, c.getId());
			result = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		}
		return result;
	}
	
//	public static void main(String[] args) {
//		CustomerDaoImpl cdi=new CustomerDaoImpl();
//		Customer c=new Customer(2, "alice", 33.11, "1", new Date(), "11111,23");
////		System.out.println(cdi.addCustomer(c));
//		System.out.println(cdi.delCustomer(4));
////		System.out.println(cdi.findAllCustomer().size());
////		System.out.println(cdi.findCustomer(2).toString());
////		System.out.println(cdi.updateCustomer(c));
//	}

}
